February 1998
By Robert Green
Microsoft offers an array of technologies for accessing data sources from your applications. These include:
This paper puts the various technologies in perspective, reviews their capabilities, and clarifies the role of each. This will give you the background and knowledge to choose the right technologies and interfaces to use, both today and tomorrow, in your database solutions.
Universal Data Access is Microsoft’s strategy for providing access to information across the enterprise. Today, companies building database solutions face a number of challenges as they seek to gain maximum business advantage from the data and information distributed throughout their corporations. Through OLE DB and ADO, Universal Data Access provides high-performance access to a variety of information sources, including relational and non-relational sources, and an easy-to-use programming interface that is tool- and language-independent. These technologies enable corporations to integrate diverse data sources, create easy-to-maintain solutions, and use their choice of the best tools, applications, and platform services.
Universal Data Access does not require companies to move data into a single data store, which is expensive and time-consuming, nor does it require that they commit to a single vendor’s products. Universal Data Access is based on open industry specifications with broad industry support, and works with all major established database platforms. Universal Data Access is an evolutionary step from today’s standard interfaces, including ODBC, RDO, and DAO; and extends the functionality of these well-known and well-tested technologies.
Universal Data Access is based on the ability of OLE DB to access data of all types, and it relies on ADO to provide the programming model that application developers will use.
More and more often, businesses are discovering the need to build solutions that span desktop, mid-range, mainframe, and Internet technologies. The variety of data stores that are present in any business today, and the many ways in which they are accessed, create challenges to developing applications that can bridge both the new and the old technologies. As a result, new requirements emerge for middleware that enables a new class of applications to be easily built and managed.
OLE DB is Microsoft’s strategic low-level interface to all kinds of data throughout the enterprise. OLE DB is an open specification designed to build on the success of ODBC by providing an open standard for accessing all kinds of data. Whereas ODBC was created to access relational databases, OLE DB is designed for relational and non-relational information sources, including mainframe ISAM/VSAM and hierarchical databases; email and file system stores; text, graphical, and geographical data; custom business objects; and more.
OLE DB components consist of data providers, which expose their data; data consumers, which use data; and service components, which process and transport data (for example, query processors and cursor engines). These components are designed to integrate smoothly to help OLE DB component vendors bring high quality OLE DB components to market quickly. OLE DB includes a bridge to ODBC to enable continued support for the broad range of ODBC relational database drivers available today.
OLE DB is a set of COM (Component Object Model) interfaces that provide applications with uniform access to data stored in diverse information sources, regardless of location or type. OLE DB is a developing industry standard for data access to and manipulation of both SQL and non-SQL data sources.
OLE DB goes beyond simple data access by partitioning the functionality of a traditional relational database into logical components. There are three main categories of components built on the OLE DB architecture: data consumers, data providers, and service components.
Data consumers are applications that need access to a broad range of data. These include development tools, languages, and personal productivity tools. An application becomes ODBC-enabled by using the ODBC API to talk to data. Similarly, an application becomes OLE DB-enabled by using the OLE DB API to talk to data. Microsoft is actively encouraging a broad set of tool vendors to write to the OLE DB specification and in the near future all of our development tools will be able to access data via OLE DB.
Data providers make their data available for consuming. They may do this by natively supporting OLE DB or they may rely on additional OLE DB data providers. If a data provider is a native OLE DB provider, an application can talk to it directly, via OLE DB. There is no need for additional drivers or software. Contrast this with ODBC, where an ODBC driver is always needed for an application to talk to the data.
A data provider that does not natively support OLE DB relies on an intermediary in the same way that ODBC data sources rely on ODBC drivers. Data providers develop this intermediary software with the OLE DB SDK. This is important because it means that you can get to data through OLE DB without moving it and without waiting for the data source to be rewritten. To get to Microsoft® Exchange data today, all you would need is a MAPI data provider. You wouldn’t need to wait for a future version of Exchange.
The OLE DB Provider for ODBC enables applications to use OLE DB to talk to relational data via ODBC. This means that you can use OLE DB today to get to all of the same data you currently use ODBC to access. The OLE DB Provider for ODBC ensures that you can continue writing high performance database applications with existing ODBC technologies and drivers.
OLE DB provides a base level data access functionality: the managing of a tabular rowset. In other words, a provider must be able to represent data in rows and columns. Service components provide additional functionality such as query processing or cursor engines. A query processor allows SQL queries to be constructed and run against the data source. A cursor engine provides scrolling capabilities for data sources that don't support scrolling.
So for example, if you wanted to query Microsoft® SQL Server™ data you would not need a service component because SQL Server has both a query processor and cursor engine. However, to query a Microsoft® Internet Information Server log file you would either build or buy a component that provided querying capabilities for text files.
ADO is Microsoft’s strategic, high-level interface to all kinds of data. ADO provides consistent, high-performance access to data, whether you’re creating a front-end database client or middle-tier business object using an application, tool, language, or even an Internet browser. ADO is the single data interface you need to know for 1- to n-tier client/server and Web-based data-driven solution development.
ADO is designed as an easy-to-use application level interface to Microsoft’s newest and most powerful data access paradigm, OLE DB. OLE DB provides high-performance access to any data source, including relational and non-relational databases, email and file systems, text and graphics, custom business objects, and more. ADO is implemented with a small footprint, minimal network traffic in key Internet scenarios, and a minimal number of layers between the front-end and data source—all to provide a lightweight, high-performance interface. ADO is easy to use because it is called using a familiar metaphor – the Automation interface, available from just about any tool and language on the market today. And since ADO was designed to combine the best features of, and eventually replace RDO and DAO, it uses similar conventions with simplified semantics to make it easy to learn for today’s developers.
ADO ships with the following products:
Since ADO is a COM-based component, any application or language capable of working with COM objects can use it. ADO is built around a set of "core" functions that all data sources are expected to implement. ADO is not specifically designed for relational or ISAM databases — but as an object interface to any data source.
ADO serves much the same purpose as DAO and RDO, that is, to give you an easy-to-use object model for accessing data. ADO allows you to access data via OLE DB and therefore is a way to programmatically work with all types of data.
Developers who have used DAO or RDO will recognize the ADO interfaces and will be able to work with them very quickly. ADO is targeted as the natural evolution of the DAO and RDO interfaces. The current version of ADO provides most, but not all, of RDO’s functionality, ADO will eventually provide a superset of RDO functionality and provide a far more sophisticated interface — in addition to an easier programming model. The goal of ADO is to become the Microsoft standard for high-level data access to all data sources in a language and platform neutral manner.
ADO provides the ability to perform data querying and manipulation, that is, SELECT, INSERT, UPDATE, and DELETE statements for all types of data. To access relational data, ADO can use OLE DB directly or it can use the OLE DB Provider for ODBC. To access other types of data, ADO will typically rely on the existence of a data provider. In the same manner that you can get to any relational data if you have the appropriate ODBC driver, you can use ADO to get to any data if you have the appropriate OLE DB provider.
Additional ADO features include:
There are currently no data definition capabilities in ADO. As mentioned earlier, ADO today provides a subset of the functionality in RDO. However, it will eventually provide a superset.
The Microsoft® Remote Data Service (RDS), formerly known as the Advanced Data Connector (ADC), is your source for distributed data access through the Web. RDS is a feature of ADO and makes full-featured, data-centric Web applications a reality by combining data manipulation of retrieved data, efficient client-side caching, and support for data-aware ActiveX controls with an elegant and powerful programming model.
RDS goes beyond the current generation of Web data access tools by allowing clients to update the data they see. Using drop-in ActiveX data controls, such as grids, lists, and combo boxes, developers can deploy sophisticated user interfaces that allow end users to view and change data with a minimum of programming. End users are no longer restricted to staring at a static HTML results table. With RDS, they can now alter, add, and delete data they have queried and retrieved. In addition, all changes are buffered locally, and can be submitted to the server for inspection, processing, and storage in the database.
The benefits of traditional client/server technology have migrated to the Web, and the read-only, static client is a thing of the past. By providing a local data cache, the end user is now able to navigate through large data sets without costly server round trips.
RDS provides the ability to invoke remote objects over HTTP and DCOM, enabling programmers to develop distributed Web applications that effectively partition application logic between Microsoft® Visual Basic®, Scripting Edition code on the client and server objects. Automation objects written in Visual Basic can expose services to client-side applications, while protecting business logic and data from distribution. Developers are no longer restricted to choosing between "thin" or "fat" clients and servers. They can make an informed choice and partition their data and business logic accordingly.
ODBC is a common application programming interface (API) for SQL data sources and provides a common interface for accessing heterogeneous SQL databases. ODBC is based on SQL as a standard for accessing data. ODBC’s consistent interface provides maximum interoperability: a single application can access different database management systems (DBMS) through a common set of code.
This enables a developer to build and distribute a client/server application without targeting a specific DBMS or having to know specific details of various back-end data sources. When an application needs to get data from a data source, the application sends a SQL statement to the ODBC Driver Manager, which then loads the ODBC driver required to talk to the data. The driver then translates the SQL sent by the application into the SQL used by the DBMS and sends it to the back-end database. The DBMS retrieves the data and passes it back to the application via the driver and the Driver Manager.
Developers can code directly to the ODBC API by declaring various functions and then using them to connect, send SQL statements, retrieve results, get errors, disconnect, and so on. This is well documented and has been a very popular way of writing Visual Basic client/server applications. However, it is fairly difficult and involves a lot of code. Because of this, ADO, RDO, and DAO are used more often to access ODBC data from applications.
ODBC has been the data access standard since 1992 and has played a very important role in enabling client/server applications. There are over 170 ODBC drivers available. With well-written drivers, ODBC performance is excellent. ODBC in the short and medium term is the best way to access a broad range of relational data due to the high number of available drivers.
The Microsoft Data Access Components consist of the latest versions of ADO, OLE DB, and ODBC, which are released, documented, and supported together. They are the key technologies that enable Universal Data Access. They provide easy-to-use, high-performance access to all types of data throughout the enterprise. Developers creating client/server and Internet/intranet-based data-driven solutions use these components to easily integrate information from a variety of sources, both relational (SQL) and non-relational.
Microsoft Data Access Components 2.0 includes:
RDO provides an object model for accessing ODBC data. The RDO programming model is similar to the DAO model, except that it is aimed at SQL databases rather than at ISAM data and therefore more emphasis is placed on stored procedures and taking advantage of server functionality.
RDO was invented to give Visual Basic developers the ability to access ODBC data without having to code to the ODBC API. It is a thin layer over the ODBC API and provides all the functionality of ODBC in an easy-to-use object model.
RDO is designed to take advantage of intelligent database servers that use sophisticated query engines, such as SQL Server and Oracle. It ships with the Enterprise Editions of Visual Basic, Visual C++, and Microsoft Visual Studio.
RDO provides all the expected data manipulation features plus a host of additional features:
DAO enables you to use a programming language to access and manipulate data in local or remote databases, and to manage databases, their objects, and their structure.
DAO provides for data access via an object model, rather than via a collection of function calls in a DLL. This makes it much easier and more understandable to make database calls. In addition, DAO hides various plumbing issues from the developer. The developer uses the objects and DAO manages the connections, result sets, and so on.
DAO supports two different database environments, or "workspaces:"
It is important to understand that ODBCDirect is part of DAO 3.5. It is an extension to DAO, not a separate entity. ODBCDirect actually uses RDO to get to ODBC data.
DAO 3.5 ships with Microsoft® Access 97, Visual Basic 5.0 or later, Microsoft® Office 97, and Visual C++ 5.0 or later.
Microsoft Jet offers a great deal of functionality, and if you use a Microsoft Jet workspace you have programmatic access to that functionality. Features available with Microsoft Jet workspaces include:
Using a Microsoft Jet workspace to access ODBC data requires not only loading the Microsoft Jet database engine but also using it as a middleman between the application and the data. ODBCDirect workspaces allow you to access database servers through ODBC, without loading the Microsoft Jet database engine. This provides better performance and also requires less memory (except in Access, where Microsoft Jet is always loaded), at the expense of some functionality, such as heterogeneous joins.
ODBCDirect provides all the expected data manipulation plus additional features, all of which are also found in RDO (this is not surprising since ODBCDirect uses RDO!). These additional features include:
For a discussion of these features see the RDO Features section earlier in the paper. Note that queries as methods and events are supported in RDO but not in ODBCDirect.
So now you know the various options you have for accessing data today. This paper has covered ADO, ODBC, RDO, DAO with Microsoft Jet workspaces, and DAO with ODBCDirect workspaces. Which should you use today?
ADO is now the standard data access language for Microsoft tools. The current versions of Internet Information Server, Internet Explorer, Visual Basic, Visual InterDev, Visual C++, and Visual J++, have all been written to use ADO as their primary data access language. The next release of Microsoft Office will do the same.
Among the many benefits of ADO is a common language for accessing data. No matter what tool you are using, you can use the same code to query and manipulate data. This allows for much greater and easier code reuse across applications than was possible in the past.
Therefore, if you are starting an application today you should use ADO, unless there are features you need today that are not available in ADO but are available in one or the alternative technologies. However, be aware that the goal of ADO is to superset DAO and RDO.
If you are using DAO or RDO you should still think about how you would move over to ADO when it supersedes these. That way, when the time comes, you will have an easier job migrating to ADO.
To help make that decision of which interface to use and also to determine if ADO meets your needs today, the following table presents a list of major features found in either ADO, DAO, or RDO.
| Feature | ADO 2.0 | DAO 3.5 | RDO 2.0 |
| Connect Asynchronously | X | X | |
| Run Queries Asynchronously | X | X | X |
| Batch Updates and Error Handling | X | X | X |
| Disconnected Recordsets | X | X | |
| Events | X | X | |
| Integration with Data Environment | X | ||
| Integration with Data binding in Visual Basic 6.0 | X | ||
| Integration with Visual Data Tools | X | ||
| Integration with Visual Basic/Visual C++ Transact-SQL Debugger | X | ||
| Integration with Visual Basic Connection Query Designer | X | ||
| Data Shaping | X | ||
| Persistent Recordsets | X | ||
| Distributed Transactions | X | X | |
| Threadsafe | X | X | X |
| Free-Threaded | X | ||
| In/Out/Return Value Parameters | X | X | X |
| Independently-created objects | X | X | X |
| MaxRows Property on Queries | X | X | X |
| Queries As Methods | X | X | |
| Return Multiple Recordsets | X | X | X |
| Efficient Microsoft Jet Database Access | X | X | |
| Compatibility from Microsoft Jet to SQL Server | X |